package com.alianzamedica.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import org.objectsearch.sqlsearch.ObjectSearch;
import org.w3c.dom.Document;

import com.alianzamedica.businessobject.Tag;
import com.alianzamedica.connection.ConnectionImpl;
import com.alianzamedica.tools.Enviroment;

/**
 * @author Carlos
 * 
 */
public class TagModel {

	/**
	 * @param tag
	 *            tag del sistema
	 * @return pregunta si el tag esta siendo usado
	 * @throws Exception
	 *             si ocurre error
	 */
	public boolean isUsed(String tag) throws Exception {
		boolean used = false;
		String sql = "SELECT  count(0) as used "
				+ "FROM prescription where prescription_tag = ? ";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setString(1, tag);
			rs = psmt.executeQuery();
			int result = 0;
			while (rs.next()) {
				result = rs.getInt(1);
				if (result == 1) {
					used = true;
				}
			}
		} finally {
			if (psmt != null) {
				try {
					psmt.close();
				} catch (Exception e) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return used;
	}

	/**
	 * pregunta si el tag existe en la base de datos.
	 * 
	 * @param tag
	 *            tag del sistema.
	 * @return verdadero si existe de otra forma regresa falso.
	 * @throws Exception
	 *             si ocurre error.
	 */
	public boolean exists(String tag) throws Exception {
		boolean used = false;
		String sql = "select count(0) from tag where tag_id = ? ";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setString(1, tag);
			rs = psmt.executeQuery();
			int result = 0;
			while (rs.next()) {
				result = rs.getInt(1);
				if (result == 1) {
					used = true;
				}
			}
		} finally {
			if (psmt != null) {
				try {
					psmt.close();
				} catch (Exception e) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return used;

	}

	/**
	 * regresa lista de tags no usados.
	 * 
	 * @param medicId
	 *            id del medico que usa los tags.
	 * @param pageNum
	 *            numero de pagina.
	 * @param pageSize
	 *            tamaño de pagina.
	 * @return tags del sistema asignados al medico.
	 * @throws Exception
	 *             si ocurre algun error.
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<Tag> getUnusedTags(Integer medicId, Integer pageNum,
			Integer pageSize) throws Exception {
		Enviroment env = Enviroment.getInstance();
		int start = (pageNum - 1) * pageSize;
		Document doc = env.getDocument();
		ArrayList<Tag> list = null;
		String sql = "select * from tag where tag_id "
				+ " not in (select prescription_tag from prescription) "
				+ " and lot_id in (select lot_id from doctor_lot where doctor_id = ?) "
				+ " limit ? , ? ";
		PreparedStatement psmt = null;
		ConnectionImpl impl = new ConnectionImpl();
		ObjectSearch search = new ObjectSearch(doc, impl);
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, medicId);
			psmt.setInt(2, start);
			psmt.setInt(3, pageSize);
			list = search.customQuery(psmt, con, Tag.class);
		} finally {
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	/**
	 * obtiene las paginas totales de los tags no usados.
	 * 
	 * @param doctorId
	 *            id del doctor.
	 * @param pageSize
	 *            tamaño de pagina.
	 * @return numero de paginas de los tags no usados.
	 */
	public Integer getTotalPagesFromUnusedTags(Integer doctorId, int pageSize) {
		String sql = "select ceil(count(0)/"
				+ pageSize
				+ " )"
				+ " from tag where lot_id in (select lot_id from doctor_lot where doctor_id = ?)"
				+ " and tag_id not in (select prescription_tag from prescription)";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		Integer totalPages = 0;
		try {
			impl.open();
			Connection con = impl.getConnection();
			System.out.println("Consulta obten tags no usados:"+sql);
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			rs = psmt.executeQuery();
			while (rs.next()) {
				totalPages = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			impl.closeGracefully(rs);
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return totalPages;
	}

	/**
	 * assigna un tag a un medico.
	 * 
	 * @param medicId
	 *            id del medico al que se asigna el tag
	 * @param lotId
	 *            id del lote al que se asigna el lote
	 * @throws Exception
	 *             si ocurre algun error
	 */
	public void assignTag(Integer medicId, Integer lotId) throws Exception {
		// DOCTOR_ID,LOT_ID
		String sql = "insert into doctor_lot " + "( DOCTOR_ID,LOT_ID )"
				+ " values (?,?)";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, medicId);
			psmt.setInt(2, lotId);
			psmt.execute();
		} finally {
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
	}

}
